"""
报表模块 - 数据访问层
"""
from datetime import datetime, timedelta
from typing import Tuple, List, Dict, Any, Optional
from common.database import DatabaseService
from common.logger import create_logger

logger = create_logger(__name__)


async def get_device_run_report(
    db: DatabaseService,
    keyword: Optional[str],
    start_date: Optional[datetime],
    end_date: Optional[datetime],
    status: Optional[str],
    manager_code: Optional[str],
    page: int,
    size: int,
) -> Tuple[int, List[Dict[str, Any]]]:
    """
    获取设备运行报表数据

    Args:
        db: 数据库服务
        keyword: 关键字（设备序列号模糊匹配）
        start_date: 开始时间（设备创建时间）
        end_date: 结束时间（设备创建时间）
        status: 设备状态（单选或空）
        manager_code: 负责人工号（单选或空）
        page: 页码
        size: 每页大小

    Returns:
        (总记录数, 报表数据列表)
    """
    try:
        # 构建WHERE条件
        where_conditions = ["m.deleted = 0"]
        params = []

        if keyword:
            where_conditions.append("m.serial_no LIKE %s")
            params.append(f"%{keyword}%")

        if start_date:
            where_conditions.append("m.created_at >= %s")
            params.append(start_date)

        if end_date:
            where_conditions.append("m.created_at <= %s")
            params.append(end_date)

        if status:
            where_conditions.append("m.status = %s")
            params.append(status)

        if manager_code:
            where_conditions.append("m.manager_code = %s")
            params.append(manager_code)

        where_clause = " AND ".join(where_conditions)

        # 查询总数
        count_sql = f"""
            SELECT COUNT(*) as total
            FROM dev_meter m
            WHERE {where_clause}
        """
        count_result = db.get_one(count_sql, tuple(params))
        total = count_result['total'] if count_result else 0

        # 查询分页数据
        # 获取昨日日期
        yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')

        data_sql = f"""
            SELECT
                addr.full_name as address,
                s.name as station_name,
                b.serial_no as box_serial_no,
                r.serial_no as rtu_serial_no,
                r.status as rtu_status,
                m.serial_no as meter_serial_no,
                m.phase as meter_phase,
                m.status as meter_status,
                COALESCE(f.daily_kwh, 0) as yesterday_kwh,
                COALESCE(f.total_kwh, 0) as total_kwh,
                u.name as manager_name
            FROM dev_meter m
            INNER JOIN dev_rtu r ON r.id = m.rtu_id AND r.deleted = 0
            INNER JOIN dev_box b ON b.id = r.box_id AND b.deleted = 0
            INNER JOIN dev_station s ON s.id = b.station_id AND s.deleted = 0
            LEFT JOIN dev_device_address da
                ON da.device_type = 'METER'
                AND da.device_id = m.id
                AND da.is_primary = 1
                AND da.deleted = 0
            LEFT JOIN base_region_address addr ON addr.id = da.address_id
            LEFT JOIN dc_meter_freeze f
                ON f.serial_no = m.serial_no
                AND f.freeze_day = %s
            LEFT JOIN base_user u ON u.user_code = m.manager_code
            WHERE {where_clause}
            ORDER BY s.created_at ASC, r.created_at ASC, m.created_at ASC
            LIMIT %s OFFSET %s
        """

        # 添加查询参数
        query_params = [yesterday] + params + [size, (page - 1) * size]
        rows = db.get_all(data_sql, tuple(query_params))

        logger.info(f"设备运行报表查询完成，总数: {total}, 当前页: {page}, 每页: {size}")
        return total, rows

    except Exception as e:
        logger.exception(f"查询设备运行报表异常: {e}")
        raise


async def get_alarm_work_report(
    db: DatabaseService,
    keyword: Optional[str],
    start_date: Optional[datetime],
    end_date: Optional[datetime],
    alarm_status: Optional[str],
    priority: Optional[str],
    page: int,
    size: int,
) -> Tuple[int, List[Dict[str, Any]]]:
    """
    获取告警工单报表数据

    Args:
        db: 数据库服务
        keyword: 关键字（设备序列号模糊匹配）
        start_date: 开始时间（告警发生时间）
        end_date: 结束时间（告警发生时间）
        alarm_status: 告警状态（单选或空）
        priority: 优先级（单选或空）
        page: 页码
        size: 每页大小

    Returns:
        (总记录数, 报表数据列表)
    """
    try:
        # 构建WHERE条件
        where_conditions = []
        params = []

        if keyword:
            where_conditions.append("m.serial_no LIKE %s")
            params.append(f"%{keyword}%")

        if start_date:
            where_conditions.append("a.raised_at >= %s")
            params.append(start_date)

        if end_date:
            where_conditions.append("a.raised_at <= %s")
            params.append(end_date)

        if alarm_status:
            where_conditions.append("a.status = %s")
            params.append(alarm_status)

        # 优先级条件放在最终查询中处理（因为是从工单表来的）
        where_clause = " AND ".join(where_conditions) if where_conditions else "1=1"

        # 查询总数（需要考虑priority过滤和设备表过滤）
        if priority:
            count_sql = f"""
                SELECT COUNT(DISTINCT a.id) as total
                FROM am_alarm a
                INNER JOIN dev_meter m ON m.serial_no = a.meter_code AND m.deleted = 0
                LEFT JOIN am_alarm_work aw ON aw.alarm_id = a.id
                LEFT JOIN wo_work_order wo ON wo.id = aw.work_order_id AND wo.deleted = 0
                WHERE {where_clause}
                    AND (wo.priority = %s OR wo.priority IS NULL)
            """
            count_params = params + [priority]
        else:
            count_sql = f"""
                SELECT COUNT(*) as total
                FROM am_alarm a
                INNER JOIN dev_meter m ON m.serial_no = a.meter_code AND m.deleted = 0
                WHERE {where_clause}
            """
            count_params = params

        count_result = db.get_one(count_sql, tuple(count_params))
        total = count_result['total'] if count_result else 0

        # 查询分页数据（使用窗口函数获取最新工单）
        priority_condition = "AND latest_work.priority = %s" if priority else ""
        priority_params = [priority] if priority else []

        data_sql = f"""
            SELECT
                alarm_type_enum.label as alarm_type,
                addr.full_name as address,
                m.serial_no as device_code,
                latest_work.title as work_title,
                alarm_status_enum.label as alarm_status,
                latest_work.priority,
                a.raised_at,
                a.recovered_at,
                latest_work.code as work_code,
                u.name as assignee_name,
                latest_work.description
            FROM am_alarm a
            INNER JOIN dev_meter m ON m.serial_no = a.meter_code AND m.deleted = 0
            LEFT JOIN dev_device_address da
                ON da.device_type = 'METER'
                AND da.device_id = m.id
                AND da.is_primary = 1
                AND da.deleted = 0
            LEFT JOIN base_region_address addr ON addr.id = da.address_id
            LEFT JOIN (
                SELECT
                    et.type,
                    ei.value,
                    ei.label
                FROM base_enum_item ei
                INNER JOIN base_enum_type et ON ei.type_id = et.id
                WHERE et.type = 'AlarmType' AND ei.status = 1
            ) alarm_type_enum ON alarm_type_enum.value = a.alarm_type
            LEFT JOIN (
                SELECT
                    et.type,
                    ei.value,
                    ei.label
                FROM base_enum_item ei
                INNER JOIN base_enum_type et ON ei.type_id = et.id
                WHERE et.type = 'AlarmStatus' AND ei.status = 1
            ) alarm_status_enum ON alarm_status_enum.value = a.status
            LEFT JOIN (
                SELECT
                    aw.alarm_id,
                    wo.id,
                    wo.code,
                    wo.title,
                    wo.priority,
                    wo.assignee_code,
                    wo.description,
                    ROW_NUMBER() OVER (PARTITION BY aw.alarm_id ORDER BY wo.created_at DESC) as rn
                FROM am_alarm_work aw
                INNER JOIN wo_work_order wo ON wo.id = aw.work_order_id AND wo.deleted = 0
            ) latest_work ON latest_work.alarm_id = a.id AND latest_work.rn = 1
            LEFT JOIN base_user u ON u.user_code = latest_work.assignee_code
            WHERE {where_clause}
                {priority_condition}
            ORDER BY a.raised_at DESC
            LIMIT %s OFFSET %s
        """

        # 添加查询参数
        query_params = params + priority_params + [size, (page - 1) * size]
        rows = db.get_all(data_sql, tuple(query_params))

        logger.info(f"告警工单报表查询完成，总数: {total}, 当前页: {page}, 每页: {size}")
        return total, rows

    except Exception as e:
        logger.exception(f"查询告警工单报表异常: {e}")
        raise
